﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[esf_sso].[P_GetOrRegisterSimilarWord]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [esf_sso].[P_GetOrRegisterSimilarWord]
GO


CREATE PROCEDURE esf_sso.P_GetOrRegisterSimilarWord
	@pWord VARCHAR (500)
	,@pWordID INT OUT
AS

	SET @pWordID = (SELECT ID FROM esf_sso.VWord WHERE Name = @pWord) 
	
	IF @pWordID IS NULL
	BEGIN
		
		SET @pWordID =
			(SELECT
				TOP 1 ID
			FROM
				(SELECT 
					ID, dbo.NeedlemanWunch (Name, @pWord) x
				FROM
					esf_sso.VWord) Z
			WHERE
				Z.x > 0.9
			ORDER BY
				Z.x DESC)
		
		IF @pWordID IS NULL
		BEGIN
		
			INSERT INTO esf_sso.BasicReference 
			(BasicReferenceTypeID, Code, Name, Extra_I, IsEnabled, CreationDateTime, UserCreationAlias)
			VALUES
			(9000, @pWord, @pWord, 'false', 1, GETDATE (), 'root')

			SET @pWordID = @@IDENTITY

		END

	END

GO